Em busca de melhorar sua eficiência operacional, foi solicitado pela Northwind Traders o relatório a seguir, abordando uma análise das condições atuais de sua estrutura de dados e formas de transformar os dados disponíveis em valor para o negócio.
Para atingir este objetivo, será utilizado um framework de análise de dados que guiará todas as etapas deste relatório. Será realizado um diagnóstico dos dados disponibilizados, apontando seu contexto, suas limitações e pontos de risco bem como sugestões de melhorias.
Será apresentada a situação atual da Northwind em relação a suas receitas e carteiras de clientes e fornecedores. Serão construídos indicadores operacionais com base nos dados disponíveis e também serão sugeridos indicadores que necessitam de dados extras (estes dados seriam adicionados no banco de dados da Northwind e sua aquisição operacionalizada nos processos da empresa)
Uma análise dos produtos e clientes mais rentáveis bem como o impacto do frete nos custos dos clientes será realizada, visando agregar informações para a otimização da cadeia logística da Northwind.
Por fim serão apresentadas sugestões e ações de melhorias que podem levar a Northwind à um patamar de excelência operacional superior ao atual.
Contexto e limitações dos dados
2.1 products.csv
2.2 customers.csv
2.3 order_details.csv
2.4 orders.csv
2.5 suppliers.csv
Distribuição Demográfica
3.1 Distribuição de clientes
3.1.1Top 10 Clientes
3.2 Distribuição dos fornecedores
3.2.1Top 10 Fornecedores
3.2.2Top 10 Produtos
3.3 Receita por categoria de produto
Indicadores Operacionais
4.1 Receita Mensal
4.2 Ticket Médio
4.3 Tempo de Envio de Pedidos
4.3.1 Prazos e Metas de envio e entrega
4.3.2 Tempo de Envio
4.3.3 Tempo de Entrega
4.4 Tempo médio entre ordens por cliente
Conclusões e Sugestões
6.1 Estrutura de Dados
6.2 Sugestões para o Negócio
Para este projeto será usada como base o framework de análise de dados abaixo:
Figura-1: Framework de análise de dados
A Northwind armazena os dados num banco relacional PostgreSQL porém neste projeto foi usado o MySQL (por questões de conveniência) para simular as condições do cliente e facilitar uma possível implementação de uma solução definitiva. Os dados foram acessados via SQL queries executadas a partir do jupyter notebook utilizando linguagem python.
Por se tratar de um projeto interno, questões de licença, anonimização, permissões e governança foram desconsideradas.
Não foram utilizados nenhuma fonte de dados aquém das já disponibilizadas.
As principais ferramentas e bibliotecas utilizadas neste projeto foram:
MySQL Workbench (desenvolvimento e teste das queries);
Jupyter Notebook (documentação e processamento dos códigos e etapas de análise)
Pandas, Matplotlib e Seaborn (manipulação dos dados e criação dos gráficos)
Bokeh (biblioteca python para criação dos gráficos interativos)
# Principais imports usados
#imports específicos usados em cada sessão serão a dicionados na prória sessão
import os
# Imports necessários para realizar conexão com o SQL database
import mysql.connector as mysql
from credentials import login_config, path_dir
# Imports relacionados a manipulação dos dados
import geopandas as gpd
import pandas as pd
import numpy as np
import datetime as dt
# Imports relacionados às bibliotecas de geração dos gráficos e plotting
from bokeh.io import output_notebook, show, output_file
from bokeh.plotting import figure
from bokeh.models import GeoJSONDataSource, LinearColorMapper, ColorBar, ColumnDataSource, NumberFormatter
from bokeh.models import HoverTool, NumeralTickFormatter, Range1d, LinearAxis, DataTable, TableColumn
from bokeh.palettes import Viridis8, Category10
from bokeh.resources import INLINE
import bokeh.io
from bokeh.models.widgets import Select
from bokeh.io import curdoc
from bokeh.layouts import column, row
from bokeh.transform import cumsum
from math import pi
# código usado para ajustar o caminho do diretório de trabalho
os.chdir(path_dir)
# função necessária para o correto funcionamento dos gráficos interativos do Bokeh no jupyter notebook
bokeh.io.output_notebook(INLINE)
Os dados utilizados foram disponibilizados conforme as tabelas abaixo:
não foi disponibilizado o esquema relacional das tabelas porém foi possível inferir o mesmo a partir das colunas chaves em cada tabela, resultando no esquema abaixo (este esquema foi usado como referência para todas as relações entre tabelas nas análises do projeto):
Figura-2: Esquema relacional das tabelas
Analisando todas as tabelas individualmente para validação dos dados e metadados tem-se alguns pontos de preocupação, dentre eles, não foi indicada a codificação dos arquivos. Assim, como existem textos originários de diversos países, vários campos podem ser corrompidos e haver perda de informação.
Foi verificado também que os dados geográficos não seguem padrões internacionais, o que pode acarretar inconsistências nas apurações. Para este projeto as colunas com informação de país foram transformadas para o padrão internacional visando maior compatibilidade destes dados com ferramentas e fontes externas.
Foram verificados os seguintes pontos de atenção que devem ser alinhados com o cliente antes que estes dados possam ser usados de forma adequada em análises futuras:
| coluna | obs |
|---|---|
| unit_price | -não foi informada qual a unidade da coluna (US$ ?); -não foi possível verificar se todos os elementos desta coluna estavam representados na mesma unidade; -foi verificada uma inconsistência a qual os valores desta coluna não coincidiam com os da coluna unit_price presente na tabela order_details; |
| units_in_stock | não foram disponibilizadas informações sobre esta coluna, como em qual data esse valor é válido |
| units_on_order | não foram disponibilizadas informações sobre esta coluna, como em qual data esse valor é válido |
| reorder_level | não foi disponibilizada informação do significado ou forma de cálculo desta coluna (quanto maior o valor, maior a quantidade de vendas para um mesmo cliente ? é uma representação percentual?) |
Esta tabela possui várias colunas despadronizadas:
- postal_code: o código postal aparenta ser o código local, que é específico de cada país, podendo resultar em inconsistências em ferramentas de plot e georeferenciamento;
- similar a postal_code, não há padronização universal ao longo das observações, exigindo trativas manuais em cada observação e podendo acarretar inconsistências;
- region: coluna possui a maioria dos campos em branco, inviabilizando qualquer análise;
Os valores da coluna unit_price divergem dos valores presentes na coluna de mesmo nome da tabela products. Como não há informação suficiente para definir o contexto deste dado em cada tabela, será usado o valor presente na tabela order_details para contabilizar os valores das receitas pois, pela natureza desta tabela, as chances destes valores estarem mais coerentes deve ser maiores.
A coluna discount não possui unidade atribuída. Como todos os valores presentes na coluna são inferiores a 1, será presumido que estes valores estão em porcentagem, assim, quando necessário, será multiplicado o valor (1-discount) para obter o valor final de cada item vendido.
As informações das colunas com dados geográficos (código postal e endereço) possuem as mesmas observações das tabelas anteriores.
A coluna freight não possui unidade definida. Assim, como existem clientes e fornecedores de diversos países, não se pode confirmar se todas as entradas de dados estão usando a mesma unidade monetária ou qual ela é (esta informação deve ser validade e conformada pois pode acarretar sérios impactos na apuração de resultados e indicadores). Assim, não será apontada qualquer moeda nos valores calculados e apresentados nas análises.
A coluna required_date é ambígua e seu significado não foi explicitado pela Northwind ao disponibilizar os dados, ela pode significar o prazo de entrega (já que a diferença entre a order_date e a required_date é de 28 dias para quase todas as ordens) porém apenas a Northwind pode confirmar esta informação.
Nenhuma ordem possui shipped_date ou required date inferior à order_date, o que sinaliza coerência na cronologia das etapas das ordens.Porém em 37 ordens, a data de shipped_date é superior à required_date, o que pode sinalizar um atraso real no envio destes pedidos.
OBS: Não consta a informação da data de entrega das ordens realizadas, assim não é possível apurar a performance da pontualidade de entrega.
As informações das colunas com dados geográficos (código postal e endereço) possuem as mesmas observações das tabelas anteriores.
Como primeiro passo, será apresentada a distribuição de clientes, fornecedores e receitas para que se possa ter uma ideia inicial do posicionamento da Northwind, o que irá auxiliar a guiar as análises e sugestões a serem realizadas.
No bloco de código abaixo estão os passo de carregamento dos dados utilizados:
# Conectando ao banco de dadso SQL e extraindo os dados sumarizados dos clientes
# os dados serão guardados em arquivos csv para manter o notebook compativel sem a conexão com o banco SQL
connection = mysql.connect(**login_config)
# Executando a SQL query para extrair os dados de clientes
with open("2-Queries/3-1_customers_by_country.sql") as query:
_customers_by_country = pd.read_sql_query(query.read(), con=connection)
_customers_by_country.to_csv("1-Datasets/query_results/customers_by_country.csv", sep=";",
decimal='.', index=False)
# Executando a SQL query para extrair os dados de receitas
with open("2-Queries/3-1_revenue_share_by_country.sql") as query:
_revenue_by_country = pd.read_sql_query(query.read(), con=connection)
_revenue_by_country.to_csv("1-Datasets/query_results/revenue_by_country.csv", sep=";",
decimal='.', index=False)
/home/hbeltrao/Hugo/py_environments/data_env/lib/python3.10/site-packages/pandas/io/sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn( /home/hbeltrao/Hugo/py_environments/data_env/lib/python3.10/site-packages/pandas/io/sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn(
customers_by_country = pd.read_csv("1-Datasets/query_results/customers_by_country.csv", sep=";", decimal='.')
revenue_by_country = pd.read_csv("1-Datasets/query_results/revenue_by_country.csv", sep=";", decimal='.')
# Carregando dados externos usados no auíxio para geração correta dos mapas geográficos,
# estes arquivos contém dados geográficos como a geometria a ser plotada e metadados para
# a correta manipulação e conversão para os datatypes necessários.
# Carregando a lista de códigos dos países no formato ISO 3166-1 alpha-3
country_codes = pd.read_csv("1-Datasets/countries-codes.csv", delimiter=';')
# Carregando os dados geográficos de todos os países, usaremos a coluna geometry
# para extrair os contornos dos países e plotar o mapa corretamente
world_map = gpd.read_file(gpd.datasets.get_path("naturalearth_lowres"))
# Juntando os dados de clientes com os dados de receitas por país
customer_data_by_country = customers_by_country.merge(revenue_by_country, how='left',
left_on='country', right_on='ship_country')
customer_data_by_country.drop('ship_country', axis=1, inplace=True)
# Definindo uma função para mapear e inserir o código ISO-3 nos datasets
def get_country_code(country):
if country in country_codes['LABEL EN'].unique():
code = country_codes.loc[country_codes['LABEL EN']==country, 'ISO3 CODE'].iloc[0]
else:
code = ""
return code
Usando a função definida acima, será inserida a informação do código ISO 3166-1 alpha-3 (padrão usado internacionalmente para reprepsentar cada país com um código exclusivo de 3 letras). devido alguns países não estarem escritos no padrão internacional, os códigos destes países serão inseridos manualmente
# Usando o método apply() para mapear a função get_country_codes() no dataset e adicionar os códigos ISO-3
customer_data_by_country['ISO_code'] = customer_data_by_country['country'].apply(lambda x: get_country_code(x))
customer_data_by_country[customer_data_by_country['ISO_code']=='']
| country | tot_customers | customer_share(%) | brute_revenue | revenue_share(%) | ISO_code | |
|---|---|---|---|---|---|---|
| 0 | USA | 13 | 14.29 | 245584.61 | 19.40 | |
| 4 | UK | 7 | 7.69 | 58971.31 | 4.66 | |
| 7 | Venezuela | 4 | 4.40 | 56810.63 | 4.49 |
# ajustando manualmente os códigos faltantes
customer_data_by_country.loc[customer_data_by_country['country']=='USA', 'ISO_code'] = 'USA'
customer_data_by_country.loc[customer_data_by_country['country']=='UK', 'ISO_code'] = 'GBR'
customer_data_by_country.loc[customer_data_by_country['country']=='Venezuela', 'ISO_code'] = 'VEN'
A biblioteca Bokeh utiliza o formato GeoJSON para plotar dados geográficos, assim, será usada a biblioteca geopandas para a correta criação e manipulação dos datasets neste formato
# Juntando os dados de clientes aos dados geográficos (serão mantidos os registros dos países
# sem clientes para que possa ser plotado o mapa completo e não apenas os países com clientes)
full_country_data_with_geodata = world_map.merge(customer_data_by_country, how='left',
right_on='ISO_code', left_on='iso_a3')
# Algumas funcionalidades do Bokeh precisam rodar num bokeh server para funcionar corretamente.
# uma forma de garantir que o gráfico seja plotado pela aplicação do bokeh e não pelo jupyter
# é encapsular a geração do gráfico numa função e chamá-la dentro da função show()
def plot_map(json_data,plot_col,title, tooltips):
geosource = GeoJSONDataSource(geojson = json_data.to_json())
palette = Viridis8
palette = palette[::-1]
color_mapper = LinearColorMapper(palette = palette)
tooltips = tooltips
color_bar = ColorBar(color_mapper=color_mapper, label_standoff=8,width = 500, height = 20,
border_line_color=None,location = (0,0), orientation = 'horizontal', title = "% de clientes")
p = figure(title = title, plot_height = 600 , plot_width = 950, tooltips = tooltips)
p.xgrid.grid_line_color = None
p.ygrid.grid_line_color = None
p.title.text_font_size = '20pt'
p.patches('xs','ys', source = geosource, fill_color = {'field' :plot_col, 'transform' : color_mapper},
line_color = 'black', line_width = 0.25, fill_alpha = 1)
p.add_layout(color_bar, 'below')
output_notebook()
show(p)
# Definindo os tooltips que serão mostrados no gráfico
TIPS = [('Country','@name'),
('Tot Customers', "@tot_customers{0,0}"),
('Customer Share', '@{customer_share(%)}{0.0} %'),
('Revenue', '@brute_revenue{($ 0.00 a)}'),
('Revenue Share', '@{revenue_share(%)}{0.0} %')]
# Chamando a função para plotagem do gráfico de fornecedores
plot_map(full_country_data_with_geodata,'customer_share(%)','Distribuição de clientes por país', TIPS)
# Plotando o ranking por receita
continent_data = full_country_data_with_geodata[full_country_data_with_geodata["ISO_code"] != 0]\
[['continent', 'tot_customers', 'customer_share(%)', 'brute_revenue', "revenue_share(%)"]]\
.groupby('continent').sum().reset_index()
continent_data.sort_values(by='revenue_share(%)', ascending=False)
| continent | tot_customers | customer_share(%) | brute_revenue | revenue_share(%) | |
|---|---|---|---|---|---|
| 3 | Europe | 54.0 | 59.36 | 774574.54 | 61.20 |
| 4 | North America | 21.0 | 23.08 | 319362.98 | 25.23 |
| 7 | South America | 16.0 | 17.59 | 171855.51 | 13.58 |
| 0 | Africa | 0.0 | 0.00 | 0.00 | 0.00 |
| 1 | Antarctica | 0.0 | 0.00 | 0.00 | 0.00 |
| 2 | Asia | 0.0 | 0.00 | 0.00 | 0.00 |
| 5 | Oceania | 0.0 | 0.00 | 0.00 | 0.00 |
| 6 | Seven seas (open ocean) | 0.0 | 0.00 | 0.00 | 0.00 |
Desta forma, percebe-se que a Europe é o maior cliente da Northwind e que não há clientes da Ásia, Africa e Oceania.
Ranking dos maiores clientes da Northwind em receita:
# Executando a SQL query para extrair o top10 de clientes
with open("2-Queries/3-1-1_revenue_share_by_customer.sql") as query:
_customers_info = pd.read_sql_query(query.read(), con=connection)
_customers_info.to_csv("1-Datasets/query_results/customers_info.csv", sep=";", decimal=".", index=False)
/home/hbeltrao/Hugo/py_environments/data_env/lib/python3.10/site-packages/pandas/io/sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn(
customers_info = pd.read_csv("1-Datasets/query_results/customers_info.csv", sep=";", decimal=".")
customers_info[:10]
| customer_id | customer_name | customer_country | brute_revenue | revenue_share(%) | tot_orders | freight_cost | freight_weight(%) | |
|---|---|---|---|---|---|---|---|---|
| 0 | QUICK | QUICK-Stop | Germany | 110277.30 | 8.71 | 28 | 5605.63 | 5.08 |
| 1 | ERNSH | Ernst Handel | Austria | 104874.98 | 8.29 | 30 | 6205.39 | 5.92 |
| 2 | SAVEA | Save-a-lot Markets | USA | 104361.95 | 8.24 | 31 | 6683.70 | 6.40 |
| 3 | RATTC | Rattlesnake Canyon Grocery | USA | 51097.80 | 4.04 | 18 | 2134.21 | 4.18 |
| 4 | HUNGO | Hungry Owl All-Night Grocers | Ireland | 49979.90 | 3.95 | 19 | 2755.24 | 5.51 |
| 5 | HANAR | Hanari Carnes | Brazil | 32841.37 | 2.59 | 14 | 724.77 | 2.21 |
| 6 | KOENE | Königlich Essen | Germany | 30908.38 | 2.44 | 14 | 813.68 | 2.63 |
| 7 | FOLKO | Folk och fä HB | Sweden | 29567.56 | 2.34 | 19 | 1678.08 | 5.68 |
| 8 | MEREP | Mère Paillarde | Canada | 28872.19 | 2.28 | 13 | 1394.22 | 4.83 |
| 9 | WHITC | White Clover Markets | USA | 27363.61 | 2.16 | 14 | 1353.06 | 4.94 |
Para verificar o alinhamento logístico entre clientes e fornecedores, será apresentada a distribuição geográfica dos fornecedores. Assim, possíveis oportunide de melhoria ficarão mais evidentes.
# Executando a SQL query para extrair os dados de fornecedores
with open("2-Queries/3-2_suppliers_by_country.sql") as query:
_suppliers_by_country = pd.read_sql_query(query.read(), con=connection)
_suppliers_by_country.to_csv("1-Datasets/query_results/suppliers_by_country.csv", sep=";",
decimal=".", index=False)
/home/hbeltrao/Hugo/py_environments/data_env/lib/python3.10/site-packages/pandas/io/sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn(
# Usando o método apply() para mapear a função get_country_codes() no dataset e adicionar os códigos ISO-3
suppliers_by_country = pd.read_csv("1-Datasets/query_results/suppliers_by_country.csv", sep=";",
decimal=".")
suppliers_by_country['ISO_code'] = suppliers_by_country['country'].apply(lambda x: get_country_code(x))
suppliers_by_country[suppliers_by_country['ISO_code']=='']
| country | suppliers | tot_products | unique_products | revenue | revenue_% | ISO_code | |
|---|---|---|---|---|---|---|---|
| 3 | USA | 4 | 277 | 12 | 122103.47 | 9.65 | |
| 6 | UK | 2 | 220 | 7 | 78432.04 | 6.20 |
# ajustando manualmente os códigos faltantes
suppliers_by_country.loc[suppliers_by_country['country']=='USA', 'ISO_code'] = 'USA'
suppliers_by_country.loc[suppliers_by_country['country']=='UK', 'ISO_code'] = 'GBR'
# Juntando os dados de fornecedores aos dados geográficos (serão mantidos os registros dos países sem fornecedores
# para que possa ser plotado o mapa completo e não apenas os países com fornecedores)
suppliers_by_country_with_geodata = world_map.merge(suppliers_by_country, how='left',
right_on='ISO_code', left_on='iso_a3')
# Definindo os tooltips que serão mostrados no gráfico
TIPS = [('Country','@name'),
('Tot Suppliers', "@suppliers{0,0}"),
('Total products sold', '@tot_products{0,0}'),
('Unique products on sale', '@unique_products{0,0}'),
('Revenue', '@revenue{($ 0.00 a)}'),
('Revenue Share', '@{revenue_%}{0.0} %')]
# Chamando a função para plotagem do gráfico de fornecedores
plot_map(suppliers_by_country_with_geodata,'revenue_%','Distribuição dos fornecedores por país de origem', TIPS)
Apesar da maioria dos fornecedores também estar presente na Europa, existem grandes fornecedores na Austrália, Japão e Estados Unidos (cerca de 25% do total de receitas).
Assim, existe uma janela de oportunidade para otimizar a cadeia de distribuição destes fornecedores e reduzir os custos de transporte e tempos de entrega. Na sessão de sugestões de ações, será feita uma análise mais profunda destes fornecedores e seus produtos, com propostas de otimização desta cadeia logística.
Ranking dos maiores fornecedores da Northwind em receita:
# Executando a SQL query para extrair o top10 de fornecedores
with open("2-Queries/3-2-1_revenue_share_by_supplier.sql") as query:
_top_10_suppliers = pd.read_sql_query(query.read(), con=connection)
_top_10_suppliers.to_csv("1-Datasets/query_results/top_10_suppliers.csv", sep=";",
decimal=".", index=False)
/home/hbeltrao/Hugo/py_environments/data_env/lib/python3.10/site-packages/pandas/io/sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn(
top_10_suppliers = pd.read_csv("1-Datasets/query_results/top_10_suppliers.csv", sep=";",
decimal=".")
top_10_suppliers[:10]
| supplier | origin_country | revenue | total_orders | unique_products | revenue_share(%) | |
|---|---|---|---|---|---|---|
| 0 | Aux joyeux ecclésiastiques | France | 153691.2750 | 54 | 2 | 12.14 |
| 1 | Plutzer Lebensmittelgroßmärkte AG | Germany | 145372.3995 | 179 | 5 | 11.48 |
| 2 | Gai pâturage | France | 117981.1800 | 105 | 2 | 9.32 |
| 3 | Pavlova, Ltd. | Australia | 106459.7755 | 163 | 5 | 8.41 |
| 4 | G'day, Mate | Australia | 65626.7700 | 98 | 3 | 5.18 |
| 5 | Forêts d'érables | Canada | 61587.5700 | 72 | 2 | 4.87 |
| 6 | Specialty Biscuits, Ltd. | UK | 59032.0800 | 164 | 5 | 4.66 |
| 7 | Pasta Buttini s.r.l. | Italy | 50254.6100 | 73 | 2 | 3.97 |
| 8 | Formaggi Fortini s.r.l. | Italy | 48225.1650 | 104 | 3 | 3.81 |
| 9 | Norske Meierier | Norway | 43141.5100 | 105 | 3 | 3.41 |
# Executando a SQL query para extrair o top10 de produtos
with open("2-Queries/3-2-2_revenue_share_by_product.sql") as query:
_top_10_products = pd.read_sql_query(query.read(), con=connection)
_top_10_products.to_csv("1-Datasets/query_results/top_10_products.csv", sep=";",
decimal=".", index=False)
/home/hbeltrao/Hugo/py_environments/data_env/lib/python3.10/site-packages/pandas/io/sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn(
top_10_products = pd.read_csv("1-Datasets/query_results/top_10_products.csv", sep=";",
decimal=".")
top_10_products[:10]
| product_name | origin_country | supplier | revenue | total_orders | revenue_share(%) | |
|---|---|---|---|---|---|---|
| 0 | Côte de Blaye | France | Aux joyeux ecclésiastiques | 141396.735 | 24 | 11.17 |
| 1 | Thüringer Rostbratwurst | Germany | Plutzer Lebensmittelgroßmärkte AG | 80368.672 | 32 | 6.35 |
| 2 | Raclette Courdavault | France | Gai pâturage | 71155.700 | 54 | 5.62 |
| 3 | Tarte au sucre | Canada | Forêts d'érables | 47234.970 | 48 | 3.73 |
| 4 | Camembert Pierrot | France | Gai pâturage | 46825.480 | 51 | 3.70 |
| 5 | Gnocchi di nonna Alice | Italy | Pasta Buttini s.r.l. | 42593.060 | 50 | 3.36 |
| 6 | Manjimup Dried Apples | Australia | G'day, Mate | 41819.650 | 39 | 3.30 |
| 7 | Alice Mutton | Australia | Pavlova, Ltd. | 32698.380 | 37 | 2.58 |
| 8 | Carnarvon Tigers | Australia | Pavlova, Ltd. | 29171.875 | 27 | 2.30 |
| 9 | Rössle Sauerkraut | Germany | Plutzer Lebensmittelgroßmärkte AG | 25696.640 | 33 | 2.03 |
# Executando a SQL query para extrair os dados das categorias de produtos
with open("2-Queries/3-3_revenue_by_product_category.sql") as query:
_product_categories_share = pd.read_sql_query(query.read(), con=connection)
_product_categories_share.to_csv("1-Datasets/query_results/product_categories_share.csv", sep=";",
decimal=".", index=False)
/home/hbeltrao/Hugo/py_environments/data_env/lib/python3.10/site-packages/pandas/io/sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn(
product_categories_share = pd.read_csv("1-Datasets/query_results/product_categories_share.csv", sep=";",
decimal=".")
product_categories_share['angle'] = product_categories_share['brute_revenue']\
/product_categories_share['brute_revenue'].sum()*2*pi
product_categories_share['color'] = Category10[len(product_categories_share['category_name'])]
p = figure(height=350, title="Receita por Categoria de Produto", toolbar_location=None,
tools="hover", tooltips="@category_name :@revenue_share{0.0} %", x_range=(-0.5, 1.0))
p.wedge(x=0, y=1, radius=0.4,
start_angle=cumsum('angle', include_zero=True), end_angle=cumsum('angle'),
line_color="white", fill_color='color', legend_field='category_name', source=product_categories_share)
p.axis.axis_label = None
p.axis.visible = False
p.grid.grid_line_color = None
show(p)
Nesta sessão serão apresentadas propostas de indicadores operacionais para acompanhamento da performance na Northwind a auxiliar na otimização de seus processos
A seguir será apresentada a evolução da receita mensal total da empresa bem como uma versão filtrável para visualização de carteiras específicas (receita mensal por cliente, por colaborador etc)
A receita mensal está calculada a partir dos valores da tabela order_details e a referência de data usada foi a coluna order_data da tabela orders
Segue abaixo o código usado para gerar as visualizações:
# Executando a SQL query para extrair os dados de receitas
with open("2-Queries/4-1_total_revenue_by_year_month.sql") as query:
_total_revenue_by_year_month = pd.read_sql_query(query.read(), con=connection)
_total_revenue_by_year_month.to_csv("1-Datasets/query_results/total_revenue_by_year_month.csv", sep=";",
decimal=".", index=False)
/home/hbeltrao/Hugo/py_environments/data_env/lib/python3.10/site-packages/pandas/io/sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn(
total_revenue_by_year_month = pd.read_csv("1-Datasets/query_results/total_revenue_by_year_month.csv", sep=";",
decimal=".")
# Convertendo a coluna year_months para o tipo datetype e melhorar a visualização no eixo do gráfico
total_revenue_by_year_month['year_months'] = pd.to_datetime(total_revenue_by_year_month['year_months'],
format='%Y%m').dt.to_period('M')
# Criando uma cópia da coluna year_months no formato string para uma melhor formatação na tooltip flutuante
total_revenue_by_year_month['date'] = total_revenue_by_year_month['year_months'].astype('str')
# Gerando o gráfico de receitas e ordens
# convertendo o dataset para ser plotado com o bokeh
data_source = ColumnDataSource(total_revenue_by_year_month)
# Criando e configurando a figura a ser plotada
p = figure(title="Evolução das receitas da Northwind", x_axis_label="Mês-Ano", y_axis_label="Receita",
plot_width=900, plot_height=400, tools="", toolbar_location=None, x_axis_type='datetime')
# Ajustando eixos e título
p.title.align = 'center'
p.title.text_font_size='12pt'
p.xaxis.ticker.desired_num_ticks = len(total_revenue_by_year_month['date'].unique())
p.yaxis.formatter=NumeralTickFormatter(format="($ 0.00 a)")
p.xaxis.major_label_orientation = "vertical"
p.border_fill_color = "whitesmoke"
p.min_border_left = 80
# Criando um lineplot com a receita mensa
a=p.line(x='year_months', y='revenue', source = data_source, color= 'red', line_width=2,
legend_label="Receita Mensal")
# Criando o segundo eixo y(para plotar o numero de ordens) e adicionando à figura
p.extra_y_ranges = {"Orders" : Range1d(start=0, end=total_revenue_by_year_month['tot_orders'].max()*1.1)}
p.add_layout(LinearAxis(y_range_name="Orders", axis_label="% Número de Ordens"), 'right')
# Criando um gráfico de barras com o número de ordens mensais
b=p.vbar(x='year_months', top='tot_orders', source=data_source,
width=dt.timedelta(days=15), alpha=0.3, color= 'blue',
legend_label="Número de Ordens", y_range_name="Orders")
# Adicionando a ferramenta de hovertool ao gráfico
p.add_tools(HoverTool(
tooltips=[
( 'date', '@date'),
( 'Revenue', '@revenue{($ 0.00 a)}'),
( 'Orders', '@tot_orders')
],
mode='vline',
renderers=[a]
))
# Configurando as cores dos eixos para coincidirem com os gráficos
p.yaxis[0].axis_line_color = "red"
p.yaxis[0].major_label_text_color = "red"
p.yaxis[0].major_tick_line_color = "red"
p.yaxis[0].minor_tick_line_color = "red"
p.yaxis[0].axis_label_text_color="red"
p.yaxis[1].axis_line_color = "blue"
p.yaxis[1].major_label_text_color = "blue"
p.yaxis[1].major_tick_line_color = "blue"
p.yaxis[1].minor_tick_line_color = "blue"
p.yaxis[1].axis_label_text_color="blue"
# Adicionando as legendas fora da área do gráfico
p.add_layout(p.legend[0], 'right')
show(p)
# Executando a SQL query para extrair os dados de receitas
with open("2-Queries/4-1_customer_revenue_by_year_month.sql") as query:
_customer_revenue_by_year_month = pd.read_sql_query(query.read(), con=connection)
_customer_revenue_by_year_month.to_csv("1-Datasets/query_results/customer_revenue_by_year_month.csv", sep=";",
decimal=".", index=False)
/home/hbeltrao/Hugo/py_environments/data_env/lib/python3.10/site-packages/pandas/io/sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn(
customer_revenue_by_year_month = pd.read_csv("1-Datasets/query_results/customer_revenue_by_year_month.csv", sep=";",
decimal=".")
# Convertendo a coluna year_months para o tipo datetype e melhorar a visualização no eixo do gráfico
customer_revenue_by_year_month['year_months'] = pd.to_datetime(customer_revenue_by_year_month['year_months'],
format='%Y%m').dt.to_period('M')
# Criando uma cópia da coluna year_months no formato string para uma melhor formatação na tooltip flutuante
customer_revenue_by_year_month['date'] = customer_revenue_by_year_month['year_months'].astype('str')
# Como alguns nem todos os clientes fizeram pedidos todos os meses, é preciso criar as entradas dos meses faltantes
# e preencher com zeros para que os gráficos sejam plotados de forma padronizada e coerente
customer_revenue_by_year_month_full = customer_revenue_by_year_month.pivot('year_months', 'company_name','revenue')\
.fillna(0).unstack().reset_index(name='revenue')
# foi preciso recriar a coluna date
customer_revenue_by_year_month_full['date'] = customer_revenue_by_year_month_full['year_months'].astype('str')
# Executando a SQL query para extrair os dados de fornecedores
with open("2-Queries/4-1_suppliers_sales_by_year_month.sql") as query:
_suppliers_sales_by_year_month = pd.read_sql_query(query.read(), con=connection)
_suppliers_sales_by_year_month.to_csv("1-Datasets/query_results/suppliers_sales_by_year_month.csv", sep=";",
decimal=".", index=False)
/home/hbeltrao/Hugo/py_environments/data_env/lib/python3.10/site-packages/pandas/io/sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn(
suppliers_sales_by_year_month = pd.read_csv("1-Datasets/query_results/suppliers_sales_by_year_month.csv", sep=";",
decimal=".")
# Convertendo a coluna year_months para o tipo datetype e melhorar a visualização no eixo do gráfico
suppliers_sales_by_year_month['year_months'] = pd.to_datetime(suppliers_sales_by_year_month['year_months'],
format='%Y%m').dt.to_period('M')
# Criando uma cópia da coluna year_months no formato string para uma melhor formatação na tooltip flutuante
suppliers_sales_by_year_month['date'] = suppliers_sales_by_year_month['year_months'].astype('str')
suppliers_sales_by_year_month_full = suppliers_sales_by_year_month.pivot('year_months', 'supplier_name','revenue')\
.fillna(0).unstack().reset_index(name='revenue')
suppliers_sales_by_year_month_full['date'] = suppliers_sales_by_year_month_full['year_months'].astype('str')
# Executando a SQL query para extrair os dados de colaboradores
with open("2-Queries/4-1_employee_sales_by_year_month.sql") as query:
_employee_sales_by_year_month = pd.read_sql_query(query.read(), con=connection)
_employee_sales_by_year_month.to_csv("1-Datasets/query_results/employee_sales_by_year_month.csv", sep=";",
decimal=".", index=False)
/home/hbeltrao/Hugo/py_environments/data_env/lib/python3.10/site-packages/pandas/io/sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn(
employee_sales_by_year_month = pd.read_csv("1-Datasets/query_results/employee_sales_by_year_month.csv", sep=";",
decimal=".")
# Convertendo a coluna year_months para o tipo datetype e melhorar a visualização no eixo do gráfico
employee_sales_by_year_month['year_months'] = pd.to_datetime(employee_sales_by_year_month['year_months'],
format='%Y%m').dt.to_period('M')
# Criando uma cópia da coluna year_months no formato string para uma melhor formatação na tooltip flutuante
employee_sales_by_year_month['date'] = employee_sales_by_year_month['year_months'].astype('str')
employee_sales_by_year_month_full = employee_sales_by_year_month.pivot('year_months', 'employee_name','revenue')\
.fillna(0).unstack().reset_index(name='revenue')
employee_sales_by_year_month_full['date'] = employee_sales_by_year_month_full['year_months'].astype('str')
# Definindo a função para plotar o gráfico das receitas mensais
def monthly_revenue(doc):
df1 = customer_revenue_by_year_month_full
df2 = employee_sales_by_year_month_full
df3 = suppliers_sales_by_year_month_full
def make_plot(source, title):
plot = figure(title=title, x_axis_label="Year-Months", y_axis_label="Revenue",
plot_width=900, plot_height=400, x_axis_type='datetime')
# Configurando título e eixos
plot.title.align = 'left'
plot.title.text_font_size='12pt'
plot.y_range = Range1d(start=0, end=source.data['revenue'].max()*1.1)
plot.yaxis.formatter=NumeralTickFormatter(format="($ 0.00 a)")
plot.xaxis.ticker.desired_num_ticks = 23
plot.xaxis.major_label_orientation = "vertical"
plot.border_fill_color = "whitesmoke"
plot.min_border_left = 80
# Adicionando uma lineplot com os dados de receita por mês-ano
plot.line(x='year_months', y='revenue', source = source,
color= 'blue', line_width=2, legend_label="Revenue")
# Adicionando a ferramenta de hovertool ao gráfico
plot.add_tools(HoverTool(
tooltips=[
( 'date', '@date'),
( 'Revenue', '@revenue{($ 0.00 a)}')
],
mode='vline'
))
# Adicionando legenda fora da área do gráfico
plot.add_layout(plot.legend[0], 'right')
return plot
# Função para atualização do gráfico quando o widget for usado
def update_plot(attr, old, new):
option = category_selector.value
selector = individual_selector.value
if option == 'company_name':
new_df = df1[df1['company_name']==selector]
plot.title.text = "Receita mensal: " + selector
source.data = ColumnDataSource.from_df(new_df)
plot.y_range.end = source.data['revenue'].max()*1.1
if option == 'employee_name':
new_df = df2[df2['employee_name']==selector]
plot.title.text = "Receita mensal: " + selector
source.data = ColumnDataSource.from_df(new_df)
plot.y_range.end = source.data['revenue'].max()*1.1
if option == 'supplier_name':
new_df = df3[df3['supplier_name']==selector]
plot.title.text = "Receita mensal: " + selector
source.data = ColumnDataSource.from_df(new_df)
plot.y_range.end = source.data['revenue'].max()*1.1
# Função para atualizar as opções do widget de individuo usando o widget category_selector
def update_options(attr, old, new):
option = category_selector.value
if option == 'company_name':
individual_selector.options = df1['company_name'].unique().tolist()
if option == 'employee_name':
individual_selector.options = df2['employee_name'].unique().tolist()
if option == 'supplier_name':
individual_selector.options = df3['supplier_name'].unique().tolist()
customer="Alfreds Futterkiste"
category_selector = Select(title="Category:", value="company_name",
options=['company_name', 'employee_name', 'supplier_name'])
individual_selector = Select(title="Individual:", value="Alfreds Futterkiste",
options=df1['company_name'].unique().tolist())
source = ColumnDataSource(data=df1[df1['company_name']==customer])
plot = make_plot(source, "Receita mensal: " + customer)
category_selector.on_change('value', update_options)
individual_selector.on_change('value', update_plot)
doc.add_root(column(row(category_selector, individual_selector), plot))
show(monthly_revenue, notebook_handle=True)
<-- descrição do ticket medio com suas finalidades -->
O ticket médio será calculado com base nos valores da tabela order_details. O valor de cada ordem será calculado conforme equação abaixo:
$$ TicketMedio = \sum_{i = 1}^{n}{\frac{(UnitPrice*quantity*(1-discount))}{n}} $$onde n representa a quantidade de elementos no universo de agregação (agregação por cliente, fornecedor, colaborador etc)
# Extraindo os dados de ticket médio por cliente serem plotados
# Executando a SQL query para extrair os dados de ticket medio mensal global
with open("2-Queries/4-2_average_ticket_global.sql") as query:
_average_ticket_monthly_global = pd.read_sql_query(query.read(), con=connection)
_average_ticket_monthly_global.to_csv("1-Datasets/query_results/average_ticket_monthly_global.csv", sep=";",
decimal=".", index=False)
/home/hbeltrao/Hugo/py_environments/data_env/lib/python3.10/site-packages/pandas/io/sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn(
average_ticket_monthly_global = pd.read_csv("1-Datasets/query_results/average_ticket_monthly_global.csv", sep=";",
decimal=".")
# Convertendo a coluna year_months para o tipo datetype e melhorar a visualização no eixo do gráfico
average_ticket_monthly_global['year_months'] = pd.to_datetime(average_ticket_monthly_global['year_months'],
format='%Y%m').dt.to_period('M')
# Criando uma cópia da coluna year_months no formato string para uma melhor formatação na tooltip flutuante
average_ticket_monthly_global['date'] = average_ticket_monthly_global['year_months'].astype('str')
# Executando a SQL query para extrair os dados de ticket medio mensal por cliente
with open("2-Queries/4-2_average_ticket_per_costumer.sql") as query:
_average_ticket_monthly_per_customer = pd.read_sql_query(query.read(), con=connection)
_average_ticket_monthly_per_customer.to_csv("1-Datasets/query_results/average_ticket_monthly_per_customer.csv",
sep=";", decimal=".", index=False)
/home/hbeltrao/Hugo/py_environments/data_env/lib/python3.10/site-packages/pandas/io/sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn(
average_ticket_monthly_per_customer = pd.read_csv("1-Datasets/query_results/average_ticket_monthly_per_customer.csv",
sep=";", decimal=".")
# Convertendo a coluna year_months para o tipo datetype e melhorar a visualização no eixo do gráfico
average_ticket_monthly_per_customer['year_months'] = pd.to_datetime\
(average_ticket_monthly_per_customer['year_months'],format='%Y%m').dt.to_period('M')
# Criando uma cópia da coluna year_months no formato string para uma melhor formatação na tooltip flutuante
average_ticket_monthly_per_customer['date'] = average_ticket_monthly_per_customer['year_months'].astype('str')
average_ticket_monthly_per_customer_full = average_ticket_monthly_per_customer\
.pivot(index=['year_months'],columns= ['company_name'], values=['average_ticket', 'orders'])\
.fillna(0).stack().reset_index()
average_ticket_monthly_per_customer_full['date'] = average_ticket_monthly_per_customer_full['year_months']\
.astype('str')
# convertendo o dataset para ser plotado com o bokeh
data_source = ColumnDataSource(average_ticket_monthly_global)
# Criando e configurando a figura a ser plotada
p = figure(title="Variação mensal do ticket médio da Northwind", x_axis_label="Year-Months",
y_axis_label="Average Ticket", plot_width=990, plot_height=400, tools="",
toolbar_location=None, x_axis_type='datetime')
# Ajustando eixos e título
p.title.align = 'center'
p.title.text_font_size='12pt'
p.xaxis.ticker.desired_num_ticks = len(average_ticket_monthly_per_customer_full['date'].unique())
p.yaxis.formatter=NumeralTickFormatter(format="($ 0.00 a)")
p.xaxis.major_label_orientation = "vertical"
p.border_fill_color = "whitesmoke"
p.min_border_left = 80
# Criando uma lineplot com o ticket médio mensal
a=p.line(x='year_months', y='average_ticket', source = data_source,
color= 'blue', line_width=2, legend_label="Average Ticket")
# Adicionando a ferramenta de hovertool ao gráfico
p.add_tools(HoverTool(
tooltips=[
( 'date', '@date'),
( 'Average Ticket', '@average_ticket{($ 0.00 a)}'),
( 'Total Orders', '@tot_orders')
],
mode='vline',
renderers=[a]
))
# Adicionando a legenda fora da área do gráfico
p.add_layout(p.legend[0], 'right')
show(p)
def average_ticket(doc):
df = average_ticket_monthly_per_customer_full
def make_plot(source, title):
plot = figure(title=title, x_axis_label="Year-Months", y_axis_label="Average Ticket",
plot_width=900, plot_height=400, x_axis_type='datetime')
# Configurando título e eixos
plot.title.align = 'left'
plot.title.text_font_size='12pt'
plot.y_range = Range1d(start=0, end=source.data['average_ticket'].max()*1.1)
plot.yaxis.formatter=NumeralTickFormatter(format="($ 0.00 a)")
plot.xaxis.ticker.desired_num_ticks = 23
plot.xaxis.major_label_orientation = "vertical"
plot.border_fill_color = "whitesmoke"
plot.min_border_left = 80
# Criando uma lineplot com os dados de ticket medio por mês-ano
plot.line(x='year_months', y='average_ticket', source = source,
color= 'blue', line_width=2, legend_label="Average Ticket")
# Adicionando a ferramenta de hovertool ao gráfico
plot.add_tools(HoverTool(
tooltips=[
( 'date', '@date'),
( 'Average Ticket', '@average_ticket{($ 0.00 a)}'),
( 'Total Orders', '@orders')
],
mode='vline'
))
# Adicionando legenda fora da área do gráfico
plot.add_layout(plot.legend[0], 'right')
return plot
def update_plot(attr, old, new):
selector = individual_selector.value
new_df = df[df['company_name']==selector]
plot.title.text = "Ticket Médio: " + selector
source.data = ColumnDataSource.from_df(new_df)
plot.y_range.end = source.data['average_ticket'].max()*1.1
customer="Alfreds Futterkiste"
individual_selector = Select(title="Individual:", value="Alfreds Futterkiste",
options=df['company_name'].unique().tolist())
source = ColumnDataSource(data=df[df['company_name']==customer])
plot = make_plot(source, "Ticket Médio: " + customer)
individual_selector.on_change('value', update_plot)
doc.add_root(column(individual_selector, plot))
show(average_ticket, notebook_handle=True)
O monitoramenteo e controle da performance logística é fundamental para o sucesso da Northwind, assim é interessante a criação e monitoramento dos seguintes indicadores:
a. Tempo de despacho (tempo entre a aprovação do pedido e a entrega do pedido à transportadora);
b. Tempo de entrega (tempo entre a entrega do produto à transportadora e o recebimento pelo cliente);
c. Cumprimento dos prazos de envio e entrega;
A adoção de prazos para envio e entrega de ordens é fundamental para uma operação contínua, fluída e controlada. Na base de dados disponibilizada não consta estas informações, assim sugere-se criar esta política de prazos com suas diretrizes e valores ou adicioná-las à base de dados caso já exista.
Com o estabelecimento dos prazos, o passo seguinte é a adoção de metas de cumprimento destes prazos. Como estes dados não constam na base disponibilizada, sugere-se adicioná-los à base ou criar e implementar estas metas.
A adoção de metas de desempenho é fundamental para obtenção de excelência operacional e para a melhoria contínua. Assim, torna-se necessário para a Northwind a criação de metas de envio e entregas a serem cumpridas. As métricas sugeridas são:
- Percentual de ordens enviadas dentro do prazo
- Percentual de ordens entregues dentro do prazo
- Tempo médio de envio de ordens (sugere-se uma curva decrescente)
Para o indicador tempo de envio, é possível calcular com os dados da tabele orders porém não há dados suficientes para a criação do indicador tempo de entrega. A data de envio é única para cada ordem, mesmo quando existem múltiplos fornecedores. Assim, um ponto de melhoria seria a captura das informações de cada fornecedor para cada ordem, o que ajudaria a identificar possíveis causas de atrasos.
O cálculo do indicador tempo de envio é feito da seguinte forma: $$ T_e = (ShippedDate - OrderDate) $$
Como a informação de envio não é segregada por fornecedor, não podemos apurar o tempo de envio de cada fornecedor. Sugere-se inserir esta informação para que possa ser criado um painel similar ao de colaboradores.
Como ferramenta auxiliar, foi criado um painel com o tempo de envio segregado por colaborador, desta forma pode-se ter um acompanhamento extra de performance interna.
como sugestão extra, pode-se criar um painel com todas as ordens abertas, o tempo decorrido desde a abertura e o tempo até o prazo limite de envio. Desta forma tem-se um melhor controle dos prazos e deve-se ocorrer uma redução nos tempos de envio.
# Executando a SQL query para extrair os dados de tempo de envio mensal
with open("2-Queries/4-3-2_average_shipping_time.sql") as query:
_average_shipping_time = pd.read_sql_query(query.read(), con=connection)
_average_shipping_time.to_csv("1-Datasets/query_results/average_shipping_time.csv",
sep=";", decimal=".", index=False)
/home/hbeltrao/Hugo/py_environments/data_env/lib/python3.10/site-packages/pandas/io/sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn(
average_shipping_time = pd.read_csv("1-Datasets/query_results/average_shipping_time.csv",
sep=";", decimal=".")
# Convertendo a coluna year_months para o tipo datetype e melhorar a visualização no eixo do gráfico
average_shipping_time['year_months'] = pd.to_datetime(average_shipping_time['year_months'],
format='%Y%m').dt.to_period('M')
# Criando uma cópia da coluna year_months no formato string para uma melhor formatação na tooltip flutuante
average_shipping_time['date'] = average_shipping_time['year_months'].astype('str')
# Adicionando uma coluna com um valor de meta ilustrativa apenas para demonstração visual
average_shipping_time['goal'] = 9.0
# Executando a SQL query para extrair os dados de tempo de envio mensal por colaborador
with open("2-Queries/4-3-2_average_shipping_time_per_employee.sql") as query:
_average_shipping_time_per_employee = pd.read_sql_query(query.read(), con=connection)
_average_shipping_time_per_employee.to_csv("1-Datasets/query_results/average_shipping_time_per_employee.csv",
sep=";", decimal=".", index=False)
/home/hbeltrao/Hugo/py_environments/data_env/lib/python3.10/site-packages/pandas/io/sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn(
average_shipping_time_per_employee = pd.read_csv("1-Datasets/query_results/average_shipping_time_per_employee.csv",
sep=";", decimal=".")
# Convertendo a coluna year_months para o tipo datetype e melhorar a visualização no eixo do gráfico
average_shipping_time_per_employee['year_months'] = pd.to_datetime(average_shipping_time_per_employee['year_months'],
format='%Y%m').dt.to_period('M')
# Criando uma cópia da coluna year_months no formato string para uma melhor formatação na tooltip flutuante
average_shipping_time_per_employee['date'] = average_shipping_time_per_employee['year_months'].astype('str')
average_shipping_time_per_employee_full = average_shipping_time_per_employee\
.pivot(index=['year_months'],columns= ['employee_name'], values=['average_shipping_time', 'orders'])\
.fillna(0).stack().reset_index()
average_shipping_time_per_employee_full['date'] = average_shipping_time_per_employee_full['year_months']\
.astype('str')
# convertendo o dataset para ser plotado com o bokeh
data_source = ColumnDataSource(average_shipping_time)
# Criando e configurando a figura a ser plotada
p = figure(title="Variação mensal do tempo de envio de pedidos", x_axis_label="Year-Months",
y_axis_label="tempo médio de envio (dias)",plot_width=900, plot_height=400,
tools="", toolbar_location=None, x_axis_type='datetime')
# Ajustando eixos e título
p.title.align = 'center'
p.title.text_font_size='12pt'
p.xaxis.ticker.desired_num_ticks = len(average_shipping_time['date'].unique())
p.yaxis.formatter=NumeralTickFormatter(format="(0.0)")
p.xaxis.major_label_orientation = "vertical"
p.border_fill_color = "whitesmoke"
p.min_border_left = 80
# Criando uma lineplot com o tempo médio de envio das ordens
a=p.line(x='year_months', y='average_shipping_time', source = data_source,
color= 'blue', line_width=2, legend_label="Average Shipping Time")
# Criando uma lineplot com uma meta ilustrativa apenas para demonstração visual
b=p.line(x='year_months', y='goal', source = data_source,
color= 'red', line_width=1, legend_label="Meta ilustrativa")
# Adicionando a ferramenta de hovertool ao gráfico
p.add_tools(HoverTool(
tooltips=[
( 'Date', '@date'),
( 'Average Shipping Time', '@average_shipping_time'),
( 'Orders', '@orders')
],
mode='vline',
renderers=[a]
))
# Adicionando a legenda fora da área do gráfico
p.add_layout(p.legend[0], 'right')
show(p)
def plot_shipping_time(doc):
df = average_shipping_time_per_employee_full
def make_plot(source, title):
plot = figure(title=title, x_axis_label="Mês-Ano", y_axis_label="tempo médio de envio (dias)",
plot_width=900, plot_height=400, x_axis_type='datetime')
# Configurando título e eixos
plot.title.align = 'left'
plot.title.text_font_size='12pt'
plot.y_range = Range1d(start=0, end=source.data['average_shipping_time'].max()*1.1)
plot.yaxis.formatter=NumeralTickFormatter(format="(0.0)")
plot.xaxis.ticker.desired_num_ticks = 23
plot.xaxis.major_label_orientation = "vertical"
plot.border_fill_color = "whitesmoke"
plot.min_border_left = 80
# Creating the line plot of the average ticket for each year-month
plot.line(x='year_months', y='average_shipping_time', source = source,
color= 'blue', line_width=2, legend_label="Average Shipping Time")
# Adicionando a ferramenta de hovertool ao gráfico
plot.add_tools(HoverTool(
tooltips=[
( 'date', '@date'),
( 'Average Shipping Time', '@average_shipping_time'),
( 'Orders', '@orders')
],
mode='vline'
))
# Adicionando legenda fora da área do gráfico
plot.add_layout(plot.legend[0], 'right')
return plot
def update_plot(attr, old, new):
selector = individual_selector.value
new_df = df[df['employee_name']==selector]
plot.title.text = "Tempo médio de envio: " + selector
source.data = ColumnDataSource.from_df(new_df)
plot.y_range.end = source.data['average_shipping_time'].max()*1.1
employee="Nancy Davolio"
individual_selector = Select(title="Individual:", value=employee, options=df['employee_name'].unique().tolist())
source = ColumnDataSource(data=df[df['employee_name']==employee])
plot = make_plot(source, "Tempo médio de envio de: " + employee)
individual_selector.on_change('value', update_plot)
doc.add_root(column(individual_selector, plot))
show(plot_shipping_time, notebook_handle=True)
O tempo de entrega serve para monitorar o desempenho das transportadoras bem como alinhar com os clientes as expectativas de prazos de entregas. Sendo assim um indicador fundamental para aumentar o grau de satisfação dos clientes.
o cálculo do tempo de entrega é a diferença entre a data de entrega à transportadora e a data de recebimento pelo cliente $$ T_e = (DeliveryDate - ShippedDate) $$
Assim, como a data de entrega ao cliente não está disponível, fica impossívem apurar o desempenho das transportadoras. Como sugestão de melhoria, deve-se adicionar a informação da data de entrega ao cliente na base de dados da Northwind.
Acompanhar de perto cada cliente é fundamental para a fidelização do mesmo. Porém isto se torma algo impraticável quando o volume de clientes se torna muito grande. Um primeiro passo seria a criação de uma forma de priorização. Para este projeto, será sugerido um painel com as seguintes informações:
- tempo médio entre ordens por cliente
- tempo decorrido desde a última ordem
para facilitar a visualização e operacionalização da ferramente, os clientes serão separados por país
# Executando a SQL query para extrair os dados de tempo médio entre ordens por cliente
with open("2-Queries/4-4_time_between_orders_by_customer.sql") as query:
_time_between_orders_by_customer = pd.read_sql_query(query.read(), con=connection)
_time_between_orders_by_customer.to_csv("1-Datasets/query_results/time_between_orders_by_customer.csv",
sep=";", decimal=".", index=False)
/home/hbeltrao/Hugo/py_environments/data_env/lib/python3.10/site-packages/pandas/io/sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn(
time_between_orders_by_customer = pd.read_csv("1-Datasets/query_results/time_between_orders_by_customer.csv",
sep=";", decimal=".")
time_between_orders_by_customer_grouped = time_between_orders_by_customer.groupby(['ship_country', 'customer_name'])\
.agg({'days_between_orders':'mean', 'days_since_last_order':'max', 'order_date':"count"}).reset_index()
time_between_orders_by_customer_grouped.rename(columns={"days_between_orders":"mean_time_between_orders",
"order_date":"orders"}, inplace=True)
def plot_mean_time_between_orders(doc):
df = time_between_orders_by_customer_grouped
def make_plot(source, title):
plot = figure(title=title, x_axis_label="Cliente", y_axis_label="tempo médio entre ordens (dias)",
plot_width=900, plot_height=400,x_range = source.data['customer_name'].tolist())
# Configurando título e eixos
plot.title.align = 'left'
plot.title.text_font_size='12pt'
plot.y_range = Range1d(start=0, end=source.data["mean_time_between_orders"].max()*1.1)
plot.yaxis.formatter=NumeralTickFormatter(format="(0.0)")
plot.xaxis.major_label_orientation = "vertical"
plot.border_fill_color = "whitesmoke"
plot.min_border_left = 80
# Creating the bar plot of the average ticket for each year-month
plot.vbar(x='customer_name', top='mean_time_between_orders', source = source,
color= 'blue', width=0.5, fill_alpha=0.5, legend_label="Tempo médio entre ordens (dias)")
# Adicionando a ferramenta de hovertool ao gráfico
plot.add_tools(HoverTool(
tooltips=[
( 'cliente', '@customer_name'),
( 'Tempo médio entre ordens', '@mean_time_between_orders'),
( 'Ordens', '@orders'),
( 'Dias desde a última ordem', '@days_since_last_order')
],
mode='vline'
))
# Adicionando legenda fora da área do gráfico
plot.add_layout(plot.legend[0], 'right')
return plot
def update_plot(attr, old, new):
selector = country_selector.value
new_df = df[df['ship_country']==selector]
plot.title.text = "Tempo médio de envio: " + selector
new_source = ColumnDataSource(data=new_df)
plot.x_range.factors=new_source.data['customer_name'].tolist()
source.data = ColumnDataSource.from_df(new_df)
plot.y_range.end = source.data["mean_time_between_orders"].max()*1.1
country= "USA"
country_selector = Select(title="País:", value=country, options=df['ship_country'].unique().tolist())
source = ColumnDataSource(data=df[df['ship_country']==country])
plot = make_plot(source, "Tempo médio entre ordens: " + country)
country_selector.on_change('value', update_plot)
doc.add_root(column(country_selector, plot))
show(plot_mean_time_between_orders, notebook_handle=True)
A Northwind trabalha com clientes e fornecedores do mundo todo, desta forma é necessária uma gestão eficiente do portifólio de produtos. nesta sessão serão avaliadas características como volume de vendas de cada produto, maiores clientes dos principais produtos e custos com fretes.
Será apresentado um painel com a lista de todos os compradores do produto selecionado. Alternativamente, também pode-se escolher o cliente e o painel mostra a lista de todos os produtos comprados por ele:
# Executando a SQL query para extrair a lista de produtos com os clientes de cada produto
with open("2-Queries/5-1_client_per_product_list.sql") as query:
_clients_per_product = pd.read_sql_query(query.read(), con=connection)
_clients_per_product.to_csv("1-Datasets/query_results/clients_per_product.csv",
sep=";", decimal=".", index=False)
/home/hbeltrao/Hugo/py_environments/data_env/lib/python3.10/site-packages/pandas/io/sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn(
clients_per_product = pd.read_csv("1-Datasets/query_results/clients_per_product.csv",
sep=";", decimal=".")
def customer_list(doc):
df = clients_per_product
columns = [
TableColumn(field="product_name", title="Product"),
TableColumn(field="origin_country", title="Product Origin Country"),
TableColumn(field="customer_id", title="Customer ID"),
TableColumn(field="ship_country", title="Destination Country"),
TableColumn(field="revenue", title="Revenue", formatter=NumberFormatter(format="$0,0.00")),
TableColumn(field="total_orders", title="Total Orders"),
TableColumn(field="revenue_share(%)", title="Revenue Share", formatter=NumberFormatter(format="0.00%"))
]
def update_table(attr, old, new):
selector = product_selector.value
new_df = df[df['product_name']==selector]
source.data = ColumnDataSource.from_df(new_df)
def update_table_2(attr, old, new):
selector = customer_selector.value
new_df = df[df['customer_id']==selector]
source.data = ColumnDataSource.from_df(new_df)
product = "Côte de Blaye"
customer = "QUICK"
customer_selector = Select(title="Cliente", value=customer, options=df['customer_id'].unique().tolist())
product_selector = Select(title = "Produto", value = product, options = df['product_name'].unique().tolist())
source = ColumnDataSource(df[df['product_name']==product])
data_table = DataTable(source=source, columns = columns, autosize_mode = "fit_viewport")
product_selector.on_change('value', update_table)
customer_selector.on_change('value', update_table_2)
doc.add_root(column(row(product_selector, customer_selector), data_table))
show(customer_list, notebook_handle=True)
Elemento crucial em operações logísticas, o frete impacta signifiativamente no custo dos clientes. Nesta sessão será apresentado um gráfico com o peso do frete no valor total de cada cliente.
# com a mesma base usada para gerar o top 10 de clientes podemos plotar o comportamento do frete de cada cliente
def plot_freight_weight_on_customers(doc):
df = customers_info
def make_plot(source, title):
plot = figure(title=title, x_axis_label="Cliente", y_axis_label="Peso do frete (%)",
plot_width=900, plot_height=400, x_range = source.data['customer_name'].tolist())
# Configurando título e eixos
plot.title.align = 'left'
plot.title.text_font_size='12pt'
plot.y_range = Range1d(start=0, end=source.data["freight_weight(%)"].max()*1.1)
plot.yaxis.formatter=NumeralTickFormatter(format="(0.0)")
plot.xaxis.major_label_orientation = "vertical"
plot.border_fill_color = "whitesmoke"
plot.min_border_left = 80
# Criando o gráfico de barras com o custo percentual em frete de cada cliente
plot.vbar(x='customer_name', top='freight_weight(%)', source = source,
color= 'blue', width=0.5, fill_alpha=0.5, legend_label="Peso do frete (%)")
# Adicionando a ferramenta de hovertool ao gráfico
plot.add_tools(HoverTool(
tooltips=[
( 'cliente', '@customer_name'),
( 'Valor total das ordens', '@brute_revenue{$ 0.00 a}'),
( 'Ordens', '@tot_orders'),
( 'Total com Frete', '@freight_cost{$ 0.00 a}'),
( 'Peso do Frete', '@{freight_weight(%)}{0.0} %')
],
mode='vline'
))
# Adicionando legenda fora da área do gráfico
plot.add_layout(plot.legend[0], 'right')
return plot
def update_plot(attr, old, new):
selector = country_selector.value
new_df = df[df['customer_country']==selector]
plot.title.text = "Peso do Frete nos clientes: " + selector
new_source = ColumnDataSource(data=new_df)
plot.x_range.factors=new_source.data['customer_name'].tolist()
source.data = ColumnDataSource.from_df(new_df)
plot.y_range.end = source.data["freight_weight(%)"].max()*1.1
country= "USA"
country_selector = Select(title="País:", value=country, options=df['customer_country'].unique().tolist())
source = ColumnDataSource(data=df[df['customer_country']==country])
plot = make_plot(source, "Peso do Frete nos clientes: " + country)
country_selector.on_change('value', update_plot)
doc.add_root(column(country_selector, plot))
show(plot_freight_weight_on_customers, notebook_handle=True)
O frete médio dos clientes varia próximo aos 5% do valor da compra. Com esta informação, pode-se traçar campanhas comerciais específicas para os clientes com custo de frete mais elevado.
Um outro ponto importante é monitorar o custo do frete de cada produto específico. Na base de dados disponibilizada o custo do frete está por ordem, assim não há como verificar com precisão satisfatória qual o impacto do custo do frete em cada produto.
Esta análise é fundamental para verificar oportunidades de otimização de logística, como criar sub-centros de distribuição de produtos (com alto volume de vendas mas com fornecedores mais isolados) em países específicos para baratear os custos de fretes e os tempos de entrega.
Sumarizando as principais informações deste relatório, tem-se duas linhas de ação que merecem atenção da Northwind, uma relacionada à estrutura dos dados da empresa e outra relacionada ao negócio em si.
Quanto à estrutura dos dados disponibilizados pela Northwind, percebeu-se algumas divergências entre tabelas, falta de padrão em alguns campos importantes, falta de dados importantes como data de recebimento da ordem pelo cliente e uma maior segregação dos dados referentes a produtos e fornecedores.
Foi percebido também uma falta de metadados referentes ao significado dos campos das tabelas bem como as unidades de cada campo. Informações temporais como data da última atualização de cada tabela também estavam faltando e isto afeta a credibilidade dos dados
A falta de dados e metadados também podem induzir a análises tendenciosas e isto compromete completamente a natureza de uma decisão data-driven
Seguem sugestões de melhorias a serem implementadas:
Padronizar as informações geográficas da base de dados:
Adicionar campos importantes que nã existem no banco de dados:
Dentro das análises relacionadas ao negócio em si, foram identificadas algumas oportunidades de melhoria, dentre elas:
Figura-3: Proposta de painel de acompanhamento de ordens em aberto
Neste apêndice estão listadas todas as SQL queries usadas no relatório, cada query está linkada à sessão a qual foi usada:
SELECT country, COUNT(customer_id) AS tot_customers,
ROUND(100*COUNT(*) / CAST( SUM(count(*)) OVER () AS FLOAT), 2) AS 'customer_share(%)'
FROM customers
GROUP BY country
ORDER BY tot_customers DESC;
3-1_revenue_share_by_country.sql
-- Query to calculate the value of all orders and aggregate per country, disconsidering freight cost, and calculating the percentual representation of each country
-- on the total value of orders in the sample.
select o.ship_country, round(sum(t.product_values), 2) as brute_revenue,
-- using a subselect to calculate the value of all orders
round(100*sum(t.product_values)/(select sum(unit_price*quantity*(1-discount)) from order_details), 2) as 'revenue_share(%)'
from orders as o
-- calculating the order value from the order_details table to later on be joined and agreggated by country
left join (select order_id, sum(unit_price*quantity*(1-discount)) as product_values
from order_details
group by order_id) as t
ON o.order_id = t.order_id
group by o.ship_country
order by brute_revenue desc;
3-1-1_revenue_share_by_customer.sql
-- Generating the ranking of top customers by order value
select o.customer_id, c.company_name as customer_name, o.ship_country as customer_country,
round(sum(t.product_values), 2) as brute_revenue,
-- using a subselect to calculate the value of all orders
round(100*sum(t.product_values)/(select sum(unit_price*quantity*(1-discount)) from order_details), 2) as 'revenue_share(%)',
count(o.order_id) as tot_orders,
round(sum(o.freight), 2) as freight_cost,
round(100*sum(o.freight)/sum(t.product_values), 2) as "freight_weight(%)"
from orders as o
-- calculating the order value from the order_details table to later on be joined and agreggated by country
left join (select order_id, sum(unit_price*quantity*(1-discount)) as product_values
from order_details
group by order_id) as t
ON o.order_id = t.order_id
left join customers as c
on o.customer_id = c.customer_id
group by o.customer_id, customer_name, customer_country
order by brute_revenue desc;
select sp.country, count(distinct sp.supplier_id) as suppliers,
count(sp.product_id) as tot_products,
count(distinct sp.product_id) as unique_products,
round(sum((od.unit_price * od.quantity)*(1 - od.discount)), 2) as revenue,
round(100*sum((od.unit_price * od.quantity)*(1 - od.discount))/(select sum((unit_price * quantity)*(1 - discount)) from order_details), 2) as "revenue_%"
from order_details as od
left join (select p.product_id, s.supplier_id, s.company_name, s.country
from products as p
left join suppliers as s
on p.supplier_id = s.supplier_id) as sp
on od.product_id = sp.product_id
left join orders as o
on od.order_id = o.order_id
group by sp.country
order by revenue desc;
3-2-1_revenue_share_by_supplier.sql
select t.supplier, t.origin_country,
sum(od.unit_price * od.quantity * (1-od.discount)) as revenue,
count(od.order_id) as total_orders,
count(distinct t.product_id) as unique_products,
-- using a subselect to calculate the value of all orders
round(100*sum(od.unit_price * od.quantity * (1-od.discount))/(select sum(unit_price*quantity*(1-discount)) from order_details), 2) as 'revenue_share(%)'
from order_details as od
left join (select p.product_id, p.product_name, s.country as origin_country, s.company_name as supplier
from products as p
left join suppliers as s
on p.supplier_id = s.supplier_id) as t
on od.product_id = t.product_id
group by t.supplier, t.origin_country
order by revenue desc;
3-2-2_revenue_share_by_product.sql
select t.product_name, t.origin_country,t.supplier,
sum(od.unit_price * od.quantity * (1-od.discount)) as revenue,
count(od.order_id) as total_orders,
round(100*sum(od.unit_price * od.quantity * (1-od.discount))/(select sum(unit_price*quantity*(1-discount)) from order_details), 2) as 'revenue_share(%)'
from order_details as od
left join (select p.product_id, p.product_name, s.country as origin_country, s.company_name as supplier
from products as p
left join suppliers as s
on p.supplier_id = s.supplier_id) as t
on od.product_id = t.product_id
group by t.product_name, t.origin_country, t.supplier
order by revenue desc;
3-3_revenue_by_product_category.sql
select t.category_name,
ROUND(sum(od.unit_price*od.quantity*(1-od.discount)), 2) as brute_revenue,
ROUND(100*sum(od.unit_price*od.quantity*(1-od.discount))/(select sum(unit_price*quantity*(1-discount))
from order_details), 2) as revenue_share
from order_details as od
left join (select p.product_id, p.product_name, c.category_name
from products as p
left join categories as c
on p.category_id = c.category_id) as t
on od.product_id = t.product_id
group by t.category_name
order by revenue_share desc;
4-1_total_revenue_by_year_month.sql
select extract(Year_Month from o.order_date) as year_months,
ROUND(sum(t.order_value), 2) as revenue,
count(o.order_id) as tot_orders
from orders as o
left join (select order_id, sum(unit_price * quantity *(1- discount)) as order_value
from order_details
group by order_id) as t
on o.order_id = t.order_id
group by year_months
order by year_months;
4-1_customer_revenue_by_year_month.sql
select extract(Year_Month from o.order_date) as year_months, c.company_name, ROUND(sum(t.order_value), 2) as revenue
from orders as o
left join (select order_id, sum(unit_price * quantity *(1- discount)) as order_value
from order_details
group by order_id) as t
on o.order_id = t.order_id
left join customers as c
on o.customer_id = c.customer_id
group by year_months, c.company_name
order by year_months, c.company_name;
4-1_employee_sales_by_year_month.sql
select extract(Year_Month from o.order_date) as year_months, CONCAT(e.first_name," ", e.last_name) as employee_name, ROUND(sum(t.order_value), 2) as revenue
from orders as o
left join (select order_id, sum(unit_price * quantity *(1- discount)) as order_value
from order_details
group by order_id) as t
on o.order_id = t.order_id
left join employees as e
on o.employee_id = e.employee_id
group by year_months, employee_name
order by year_months, employee_name;
4-1_suppliers_sales_by_year_month.sql
select extract(Year_Month from o.order_date) as year_months, t.company_name as supplier_name,
sum(od.unit_price * od.quantity * (1 - od.discount)) as revenue
from order_details as od
left join orders as o
on od.order_id = o.order_id
left join (select p.product_id, s.company_name
from products as p
left join suppliers as s
on p.supplier_id = s.supplier_id) as t
on od.product_id = t.product_id
group by year_months, supplier_name
order by year_months;
select extract(Year_Month from o.order_date) as year_months, ROUND(AVG(t.order_value), 2) as average_ticket,
COUNT(o.order_id) as tot_orders
from orders as o
left join (select order_id, sum(unit_price * quantity *(1- discount)) as order_value
from order_details
group by order_id) as t
on o.order_id = t.order_id
group by year_months
order by year_months;
4-2_average_ticket_per_costumer.sql
select extract(Year_Month from o.order_date) as year_months, c.company_name,
ROUND(AVG(t.order_value), 2) as average_ticket,
count(o.order_id) as orders
from orders as o
left join (select order_id, sum(unit_price * quantity *(1- discount)) as order_value
from order_details
group by order_id) as t
on o.order_id = t.order_id
left join customers as c
on o.customer_id = c.customer_id
group by year_months, c.company_name
order by year_months;
4-3-2_average_shipping_time.sql
select EXTRACT(Year_Month from order_date) as year_months,
-- Using timestampdiff() to have more precision and correctly calculate when the difference is less than an day
ROUND(AVG(timestampdiff(HOUR, order_date, shipped_date)/24), 2) AS average_shipping_time,
count(order_id) as orders
from orders
group by year_months
order by year_months;
4-3-2_average_shipping_time_per_employee.sql
select EXTRACT(Year_Month from o.order_date) as year_months, CONCAT(e.first_name," ", e.last_name) as employee_name,
-- Using timestampdiff() to have more precision and correctly calculate when the difference is less than an day
ROUND(AVG(timestampdiff(HOUR, o.order_date, o.shipped_date)/24), 2) AS average_shipping_time,
count(o.order_id) as orders
from orders as o
left join employees as e
on o.employee_id = e.employee_id
group by year_months, employee_name
order by year_months;
4-4_time_between_orders_by_customer.sql
select o.order_id, o.ship_country, c.company_name as customer_name, o.order_date,
CASE WHEN (datediff(o.order_date, LAG(o.order_date, 1) over (partition by o.customer_id order by o.order_date asc))) IS NOT NULL
THEN (datediff(o.order_date, LAG(o.order_date, 1) over (partition by o.customer_id order by o.order_date asc)))
ELSE 0 END as days_between_orders,
datediff(curdate(), max(o.order_date)over (partition by o.customer_id)) as days_since_last_order
from orders as o
left join customers as c
on o.customer_id = c.customer_id
order by customer_name, o.order_date ASC;
5-1_client_per_product_list.sql
select t.product_name, t.origin_country, o.customer_id, o.ship_country,
sum(od.unit_price * od.quantity * (1-od.discount)) as revenue,
count(od.order_id) as total_orders,
round(sum(od.unit_price * od.quantity * (1-od.discount))/(select sum(unit_price*quantity*(1-discount)) from order_details), 4) as 'revenue_share(%)'
from order_details as od
left join (select p.product_id, p.product_name, s.country as origin_country, s.company_name as supplier
from products as p
left join suppliers as s
on p.supplier_id = s.supplier_id) as t
on od.product_id = t.product_id
left join orders as o
on od.order_id = o.order_id
group by t.product_name, t.origin_country, o.customer_id, o.ship_country
order by revenue desc, t.product_name desc;